package www.worq.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import www.worq.controller.beans.partidaBean;

public class partidaDao extends oreqDao {

	public ArrayList<partidaBean> getPARTIDAS() throws SQLException {
		ArrayList<partidaBean> partidas = new ArrayList<partidaBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("select * from oreq.partida where id_recibo in (SELECT id_recibo FROM oreq.recibo where status='CALIFICACION') order by id_partida");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.partidas.getPARTIDAS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				partidaBean partida = new partidaBean();
				partida.setId_partida(rs.getInt(1));
				partida.setId_recibo(rs.getInt(2));
				partida.setMatricula(rs.getString(3));
				partida.setTomo(rs.getString(4));
				partida.setFoja(rs.getString(5));
				partida.setLegajo(rs.getString(6));
				partida.setAno(rs.getString(7));
				partida.setFideicomiso(rs.getString(8));
				partida.setHerencia(rs.getString(9));
				partida.setReposicion(rs.getString(10));
				partida.setFecha(rs.getString(11));
				partida.setAnotacion(rs.getString(12));
				partidas.add(partida);
			}
			System.out.println("dao.partidas.getPARTIDAS----------> Cargado");
		} catch (Exception e) {
			System.out.println("partidasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return partidas;
	}

	public ArrayList<partidaBean> getENTREGADOS() throws SQLException {
		ArrayList<partidaBean> partidas = new ArrayList<partidaBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("select * from oreq.partida where id_recibo in (SELECT id_recibo FROM oreq.recibo where status='P-APROVACION') order by id_partida");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.partidas.getPARTIDAS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				partidaBean partida = new partidaBean();
				partida.setId_partida(rs.getInt(1));
				partida.setId_recibo(rs.getInt(2));
				partida.setMatricula(rs.getString(3));
				partida.setTomo(rs.getString(4));
				partida.setFoja(rs.getString(5));
				partida.setLegajo(rs.getString(6));
				partida.setAno(rs.getString(7));
				partida.setFideicomiso(rs.getString(8));
				partida.setHerencia(rs.getString(9));
				partida.setReposicion(rs.getString(10));
				partida.setFecha(rs.getString(11));
				partida.setAnotacion(rs.getString(12));
				partidas.add(partida);
			}
			System.out.println("dao.partidas.getPARTIDAS----------> Cargado");
		} catch (Exception e) {
			System.out.println("partidasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return partidas;
	}

	public void getPARTIDA(partidaBean partida) throws SQLException {
		try {
			StringBuilder query = new StringBuilder();
			query.append("select * from oreq.partida where id_recibo='")
					.append(partida.getId_recibo()).append("'");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.partidas.getPARTIDAS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				partida.setId_partida(rs.getInt(1));
				partida.setId_recibo(rs.getInt(2));
				partida.setMatricula(rs.getString(3));
				partida.setTomo(rs.getString(4));
				partida.setFoja(rs.getString(5));
				partida.setLegajo(rs.getString(6));
				partida.setAno(rs.getString(7));
				partida.setFideicomiso(rs.getString(8));
				partida.setHerencia(rs.getString(9));
				partida.setReposicion(rs.getString(10));
				partida.setFecha(rs.getString(11));
				partida.setAnotacion(rs.getString(12));
			}
			System.out.println("dao.partidas.getPARTIDAS----------> Cargado");
		} catch (Exception e) {
			System.out.println("partidasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}

	public int nextId() {
		int id = 0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select id_partida from partida");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.partidas.nextId.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				if (id < rs.getInt(1)) {
					id = rs.getInt(1);
				}
			}
			System.out.println("dao.partidas.nextId----------> Cargado");
		} catch (Exception e) {
			System.out.println("partidasDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return id + 1;
	}

	public void insertPARTIDA(partidaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("INSERT INTO partida ")
					.append("(id_partida, id_recibo, matricula, tomo, foja, legajo, ano, fideicomiso, herencia, reposicion, fecha, anotacion) ")
					.append("values ('").append(bean.getId_partida())
					.append("','").append(bean.getId_recibo()).append("','")
					.append(bean.getMatricula()).append("','")
					.append(bean.getTomo()).append("','")
					.append(bean.getFoja()).append("','")
					.append(bean.getLegajo()).append("','")
					.append(bean.getAno()).append("','")
					.append(bean.getFideicomiso()).append("','")
					.append(bean.getHerencia()).append("','")
					.append(bean.getReposicion()).append("','")
					.append(bean.getFecha()).append("','")
					.append(bean.getAnotacion()).append("');");
			System.out
					.println("dao.partidas.insertPARTIDA.Ejecutado----------> "
							+ query.toString());
			st = conn.createStatement();
			st.executeUpdate(query.toString());

			System.out
					.println("dao.partidas.insertPARTIDA----------> insertado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void deletePARTIDA(partidaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("DELETE FROM partida where id_partida=").append(
					bean.getId_partida());

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.partidas.deletePARTIDA.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.partidas.deletePARTIDA----------> eliminado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updatePARTIDA(partidaBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE partida SET ").append("tomo='")
					.append(bean.getTomo()).append("',foja='")
					.append(bean.getFoja()).append("',legajo='")
					.append(bean.getLegajo()).append("',fideicomiso='")
					.append(bean.getFideicomiso()).append("',herencia='")
					.append(bean.getHerencia()).append("',reposicion='")
					.append(bean.getReposicion()).append("',fecha='")
					.append(bean.getFecha()).append("',ano='")
					.append(bean.getAno()).append("',anotacion='")
					.append(bean.getAnotacion()).append("' where id_partida=")
					.append(bean.getId_partida()).append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.partidas.updatePARTIDA.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.partidas.updatePARTIDA----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}
}
